package org.anyline.simple.dml;

import org.anyline.adapter.KeyAdapter;
import org.anyline.data.jdbc.adapter.JDBCAdapter;
import org.anyline.data.param.ConfigStore;
import org.anyline.data.param.init.DefaultConfigStore;
import org.anyline.entity.DataRow;
import org.anyline.entity.DataSet;
import org.anyline.metadata.*;
import org.anyline.proxy.ServiceProxy;
import org.anyline.service.AnylineService;
import org.anyline.simple.metadata.MetadataApplication;
import org.anyline.util.ConfigTable;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.*;

@SpringBootTest(classes = MetadataApplication.class)
public class MedtadataTest {
    private Logger log = LoggerFactory.getLogger(MedtadataTest.class);
    private static String seq = null;
    @Autowired
    private AnylineService service          ;

    @Test
    public void meta_navi(){
        ConfigStore configs = new DefaultConfigStore();
        configs.page(1, 10);
        LinkedHashMap<String, Column> columns = service.metadata().columns(new Table("crm_user"), configs);
        for(Column column:columns.values()){
            System.out.println(column);
        }
    }

    public void check(String ds, String title) throws Exception{
        System.out.println("=============================== START " + title + "=========================================");
        if(null != ds) {
            service = ServiceProxy.service(ds);
        }
        seq = null;
        if("oracle".equals(ds)){
            seq = "SIMPLE_SEQ";
            if(service.querys("USER_SEQUENCES","SEQUENCE_NAME:" + seq).size()>0) {
                service.execute("DROP SEQUENCE " + seq);
            }
            String sql = "CREATE SEQUENCE "+seq+" MINVALUE 0 START WITH 0 NOMAXVALUE INCREMENT BY 1 NOCYCLE CACHE 100";

            service.execute(sql);
        }
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        ConfigTable.IS_METADATA_IGNORE_CASE = false;
        LinkedHashMap<String, Database> dbs = service.metadata().databases();
        //schemas();
        //init("CRM_USER");
        //list();
        //json();
        tables();
        //table();
        //column();
        //view();
        //tag();
        //index();
        //function();
        //exception();
        System.out.println("=============================== END " + title + "=========================================");
    }
    @Test
    public void columnOriginalName(){
        LinkedHashMap<String, Column> columns = service.metadata("SELECT ID AS USER_ID, ID FROM CRM_USER");
        for(Column column:columns.values()){
            System.out.println("column name:" + column.getName());
            System.out.println("column original name:" + column.getOriginName());
        }
    }
    @Test
    public void tables_column(){
        LinkedHashMap<String,Table> tables = service.metadata().tables(Table.TYPE.NORMAL.value, true);
        for(Table table:tables.values()){
            System.out.println(table);
        }
    }
    @Test
    public void table2json(){
        Table table = service.metadata().table("CRM_USER");
        String json = table.json();
        System.out.println(json);
    }
    @Test
    public void columns() throws Exception {
        Table table = service.metadata().table("columns_table", false);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table("columns_table");
        table.addColumn("ID", "INT").setPrimary(true);
        for(int i=0; i<32; i++){
            table.addColumn("code"+i, "int");
        }
        service.ddl().create(table);
        ConfigStore configs = new DefaultConfigStore().page(2, 3);
        LinkedHashMap<String, Column> columns = service.metadata().columns("columns_table", configs);
        for(Column column:columns.values()){
            System.out.println(column);
        }
    }


    @Test
    public void cache(){
        Table table = service.metadata().table("CRM_USER");
        LinkedHashMap<String, Table> tables = service.metadata().tables();
        for(int i=0; i<100; i++) {
            table = service.metadata().table("CRM_USER");
            tables = service.metadata().tables();
        }
    }
    @Test
    public void constraints(){
        LinkedHashMap<String, Constraint> constraints = service.metadata().constraints("crm_user");
        System.out.println(constraints);
    }
    @Test
    public void blob() throws Exception {
        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        String name = "tab_blob";
        Table table = service.metadata().table(name);
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table(name);
        table.addColumn("ID", "LONG");
        table.addColumn("USER_IMG", "BLOB");
        service.ddl().create(table);
        DataRow row = new DataRow();
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123");
        service.insert(table, row);

        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
        row.put("ID", System.currentTimeMillis());
        row.put("USER_IMG", "ABC123".getBytes());
        service.insert(table, row);
    }
    @Test
    public void prop() throws Exception{
        String name = "tab_prop";
        init(name);
        Table table = service.metadata().table(name);
        LinkedHashMap<String, Column> columns = table.getColumns();
        for(Column column:columns.values()){
            System.out.println(column.getName());
            System.out.println("type:"+column.getFullType());
            System.out.println("nullable:"+column.isNullable());
        }
    }
    @Test
    public void json(){
        String json = "[{\"id\":1, \"userId\":11,\"userName\":\"张三\"},{\"id\":2, \"userId\":22,\"userName\":\"张三三\"}]";
        DataSet users = DataSet.parseJson(KeyAdapter.KEY_CASE.SRC, json)
                .camel_()  //驼峰转下划线
                .setPrimaryKey("id"); //设置主键
        service.save("crm_user", users);

    }
    @Test
    public void list(){
        List list = new ArrayList();
        Map m1 = new HashMap();
        m1.put("id",1);
        m1.put("userId",11);
        m1.put("userName", "张三");
        list.add(m1);
        Map m2 = new HashMap();
        m2.put("id",2);
        m2.put("userId",22);
        m2.put("userName", "张三三");
        list.add(m2);
        ConfigTable.IS_UPPER_KEY = false;
        DataSet users = new DataSet(KeyAdapter.KEY_CASE.SRC, list)
                .camel_()  //驼峰转下划线
                .setPrimaryKey("id"); //设置主键
        service.save("crm_user", users);

    }
    @Test
    public void schemas(){
        LinkedHashMap<String, Schema> schemas = service.metadata().schemas();
        System.out.println(schemas);
    }
    @Test
    public void indexes(){
        LinkedHashMap<String, Table> tables = service.metadata().tables(Table.TYPE.NORMAL.value, true);
        for(Table table:tables.values()){
            System.out.println("table:"+table.getName());
            LinkedHashMap<String, Index> indexes = table.getIndexes();
            for(Index index:indexes.values()){
                System.out.println("table index:"+index.getName());
                LinkedHashMap<String, Column> columns = index.getColumns();
                for(Column column:columns.values()){
                    System.out.println("index column:"+column.getName());
                }
            }
            LinkedHashMap<String, Column> columns = table.getColumns();
            for(Column column:columns.values()){
                System.out.println("table column:"+column.getName());
            }
        }
    }
    /**
     * 初始货表
     * @param name 表名
     * @return Table
     */
    public Table init(String name) throws Exception{
        //查询表结构
        Table table = service.metadata().table(name, false); //false表示不加载表结构，只简单查询表名
        //如果已存在 删除重键
        if(null != table){
            service.ddl().drop(table);
        }
        table = new Table<>(name).setComment("表备注");
        table.addColumn("ID", "bigint").primary(true).autoIncrement(true).setComment("主键");
        table.addColumn("CODE", "varchar(20)").setComment("编号").setNullable(false);
        table.addColumn("NAME", "varchar(50)").setComment("名称").setNullable(false);
        table.addColumn("QTY", "int").setComment("数量").setNullable(false);
        table.addColumn("O_NAME", "varchar(50)").setComment("原列表");
        table.addColumn("SALARY", "decimal(10,2)").setComment("精度").nullable(false);
        table.addColumn("DEL_COL", "varchar(50)").setComment("删除");
        table.addColumn("CREATE_TIME", "datetime").setComment("创建时间").setDefaultValue(JDBCAdapter.SQL_BUILD_IN_VALUE.CURRENT_DATETIME);
        service.ddl().save(table);
        table = service.metadata().table(name);
        Index index = new Index();
        index.addColumn("SALARY");
        index.addColumn("CODE");
        index.setName("IDX_SALARY_CODE_"+name);
        index.setUnique(true);
        index.setTable(table);
        service.ddl().add(index);
        return table;
    }
    @Test
    public void function(){
        LinkedHashMap<String, Function> functions = service.metadata().functions();
        for(Function function:functions.values()){
            System.out.println(function.getName());
            System.out.println(function.getDefinition());
            //因为部分数据库ddl可能有多条，特别是表,所以这里用list
            //执行以下命令后function中ddl属性会被赋值，所以可以直接从function中获取ddl或ddls
            List<String> ddls = service.metadata().ddl(function);
            System.out.println(function.getDdl());
        }
        LinkedHashMap<String, Procedure> procedures = service.metadata().procedures();
        for(Procedure procedure:procedures.values()){
            System.out.println(procedure.getName());
            System.out.println(procedure.getDefinition());
        }
    }
    @Test
    public void table() throws Exception{
        System.out.println("--、------------------------------ start  stable  ------------------------------------------");

        ConfigTable.IS_SQL_DELIMITER_OPEN = true;
        Table table = service.metadata().table("hr_department");
        try {
            if(null != table){
                service.ddl().drop(table);
            }
            table = new Table("hr_department");
            table.addColumn("ID", "INT").primary(true).autoIncrement(true);
            table.addColumn("NM", "varchar(50)");
            table.addColumn("REG_TIME", "datetime");
            table.addColumn("DATA_STATUS", "int");
            table.addColumn("QTY", "int");
            service.ddl().create(table);
        }catch (Exception e){
            e.printStackTrace();
        }

        List<String> ddls = service.metadata().ddl("hr_department");
        for(String ddl:ddls){
            System.out.println(ddl);
        }
        DataRow row = new DataRow();
        row.put("NM","TEST");
        row.put("AGE","20");
        if(null != seq){
            row.put("ID", "${"+seq+".NEXTVAL}");
        }

        try {
            //AGE 属性在表中不存在,直接插入会SQL异常
            //	service.insert("hr_department", row);
        }catch (Exception e){
            log.error("AGE 属性在表中不存在,直接插入会SQL异常:"+e.getMessage());
        }

        ConfigTable.IS_AUTO_CHECK_METADATA = true;
        //开启检测后，会先检测表结构，将不表中未出现的列过滤
        row.remove("ID");

        if(null != seq){
            row.put("ID", "${"+seq+".NEXTVAL}");
        }

        service.insert("hr_department", row);
        row.put("TMP_COLUMN","TMP");
        service.save("hr_department", row);


        row.remove("ID");
        if(null != seq){
            row.put("ID", "${"+seq+".NEXTVAL}");
        }

        //相同的表结构会有一段时间缓存，不会每次都读取物理表
        service.insert("hr_department", row);

        row.put("REG_TIME","");						//datetime 类型转换失败会按null处理
        row.put("AGE",1);							//数据库中没有的列 不会参与更新
        row.put("QTY","");							//int 类型转换失败会按null处理
        row.put("DATA_STATUS","1");					//int 类型转换成int
        service.save("hr_department", row);

        DataSet set = new DataSet();
        for(int i=0; i<10; i++){
            DataRow r = new DataRow();
            r.put("NM", "n_"+i);
            set.add(r);
        }
        service.insert("hr_department", set);

        //所有表名,支持模糊匹配
        List<String> tables = service.tables();
        System.out.println(tables);
        tables = service.tables(1);
        System.out.println(tables);
        tables = service.tables("bs_%",1);
        System.out.println(tables);
        tables = service.tables("root","bs_%",1);
        System.out.println(tables);

        //所有表(不包含列、索引等结构)
        LinkedHashMap<String, Table> tbls = service.metadata().tables();
        //表结构(不包含列、索引等结构)
        table = service.metadata().table("hr_department");
        LinkedHashMap<String, Column> columns = table.getColumns();
        System.out.println(table.getName()+" 属性:");
        for(Column column:columns.values()){
            System.out.println("\t"+column.toString());
        }
        columns= service.metadata().columns(tbls.get("HR_DEPARTMENT"));
        for(Column column:columns.values()){
            System.out.println("\t"+column.toString());
        }
        LinkedHashMap<String, Column> pks = table.primarys();

        System.out.println(table.getName()+" 主键:");
        for(Column column: pks.values()){
            System.out.println("\t"+column.toString());
        }

        LinkedHashMap<String, Index> indexes = table.getIndexes();
        for(Index index:indexes.values()){
            System.out.println(table.getName()+"所引:"+index.getName()+ " 类型:"+index.getType()+ " 唯一:"+index.isUnique()+" 包含列:");
            columns = index.getColumns();
            for(Column column:columns.values()){
                System.out.println("\t"+column.toString());
            }
        }
        System.out.println("-------------------------------- end  stable  --------------------------------------------");
    }
    @Test
    public void tag() throws Exception{
        System.out.println("-------------------------------- start tag  ----------------------------------------------");
        System.out.println("-------------------------------- end tag  ------------------------------------------------");
    }
    @Test
    public void index() throws Exception{
        System.out.println("-------------------------------- start index  --------------------------------------------");
        Table table = service.metadata().table("CRM_USER");

        LinkedHashMap<String,Index> indexes = table.getIndexes();
        //或者直接查询
        indexes = service.metadata().indexes("CRM_USER");
        for(Index item:indexes.values()){
            System.out.println("所引:"+item.getName());
            System.out.println("是否主键:"+item.isPrimary());
            System.out.println("是否物理所引:"+item.isCluster());
            System.out.println("是否唯一:"+item.isUnique());
            LinkedHashMap<String, Column> columns = item.getColumns();
            for(Column column:columns.values()){
                System.out.println("包含列:"+column.getName());
            }
        }
        System.out.println("-------------------------------- end index  ---------------------------------------------");
    }
    @Test
    public void view() throws Exception{
        Table table = service.metadata().table("hr_department");
        if(null == table){
            table = new Table("hr_department");
            table.addColumn("ID", "INT").primary(true).autoIncrement(true);
            table.addColumn("NM", "varchar(50)");
            table.addColumn("REG_TIME", "datetime");
            table.addColumn("DATA_STATUS", "int");
            table.addColumn("QTY", "int");
            table.addColumn("REG_DATE", "DATE");
            service.ddl().create(table);
        }
        View view = service.metadata().view("v_hr_department");
        if(null != view){
            service.ddl().drop(view);
        }
        view = new View("v_hr_department");
        view.setDefinition("SELECT * FROM HR_DEPARTMENT");
        view.setComment("视图备注");
        service.ddl().create(view);
        Map<String,View> views = service.metadata().views();
        System.out.println(views);
    }
    @Test
    public void tables() throws Exception{
        //init("HR_EMPLOYEE");
        LinkedHashMap<String,Table> ts = service.metadata().tables(null, new Schema("simple"),"test", 1);
        List<Table> list = service.metadata().tables(true, null,   "TAB_A", 1);
        for(Table table:list){
            System.out.println(table+":"+table.getComment());
        }
        ConfigStore configs = new DefaultConfigStore();
        configs.page(2,10);
        service.metadata().tables(configs);
        System.out.println("-------------------------------- start tables  --------------------------------------------");
        LinkedHashMap<String,Table> tables = service.metadata().tables();
        for(String key:tables.keySet()){
            Table table = tables.get(key);
            log.warn("table:"+table.getName());
            log.warn("comment:"+table.getComment());
        }

        //当前schema中没有的表 默认查不到
        Table table = service.metadata().table("art_comment");
        if(null != table) {
            System.out.println(table.getCatalog() + ":" + table.getSchema() + ":" + table.getName());
        }
        //当前schema中没有的表 greedy=rue 可以查到其他schema中的表
        table = service.metadata().table(true,"art_comment");
        if(null != table) {
            System.out.println(table.getCatalog() + ":" + table.getSchema() + ":" + table.getName());
        }

        System.out.println("-------------------------------- end tables  ----------------------------------------------");
    }
    @Test
    public void column() throws Exception{
        System.out.println("-------------------------------- start column  -------------------------------------------");
        Column col = service.metadata().column("hr_department","ID");
        if(null != col) {
            log.warn("column: ID, type:{}", col.getFullType());
        }
        LinkedHashMap<String,Column> columns = service.metadata().columns("b_array");
        for(Column column: columns.values()){
            log.warn("column:{}\ttype:{}\tauto increment:{}",column.getName(), column.getFullType(), column.isAutoIncrement()==1);
        }
        System.out.println("-------------------------------- end column  --------------------------------------------");
    }
    public void exception() throws Exception{
        System.out.println("-------------------------------- start exception  ----------------------------------------");
        System.out.println("-------------------------------- end  exception  -----------------------------------------");
    }


}
